﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NetMicro.ExcelMapper.Attributes;

namespace NetMicro.ExcelMapper
{
    /// <summary>
    /// 将对象属性映射到Excel列，支持导入和导出到文件
    /// </summary>
    public class ExcelMapper
    {
        #region Fields


        /// <summary>
        /// 当前工作簿
        /// </summary>
        private IWorkbook _workbook;

        /// <summary>
        /// 列过滤
        /// </summary>
        private Func<IColumnInfo, bool> _columnFilter;
        /// <summary>
        /// 导入解析
        /// </summary>
        private Func<IColumnInfo, object, bool> _defaultTakeResolver;
        /// <summary>
        /// 导出解析
        /// </summary>
        private Func<IColumnInfo, object, bool> _defaultPutResolver;
        /// <summary>
        /// 标题单元格操作
        /// </summary>
        private Action<ICell> _headerAction;


        #endregion

        #region Properties
        /// <summary>
        /// 存储缓存的内置样式，以避免为每个单元格创建新的ICellStyle
        /// </summary>
        private readonly Dictionary<short, ICellStyle> BuiltinStyles = new Dictionary<short, ICellStyle>();

        /// <summary>
        /// 存储缓存的自定义样式，以避免为每个自定义单元格创建新的ICellStyle
        /// </summary>
        private readonly Dictionary<string, ICellStyle> CustomStyles = new Dictionary<string, ICellStyle>();


        /// <summary>
        /// 存储类型而不是特定属性的格式
        /// </summary>
        internal readonly Dictionary<Type, string> TypeFormats = new Dictionary<Type, string>();

        /// <summary>
        /// 属性信息映射到列属性
        /// </summary>
        private Dictionary<PropertyInfo, ColumnAttribute> Attributes { get; } = new Dictionary<PropertyInfo, ColumnAttribute>();

        /// <summary>
        /// 属性名映射到列属性
        /// </summary>
        private Dictionary<string, ColumnAttribute> DynamicAttributes { get; } = new Dictionary<string, ColumnAttribute>();

        /// <summary>
        /// 缓存跟踪对象
        /// </summary>
        private Dictionary<string, Dictionary<Type, List<object>>> TrackedColumns { get; } =
            new Dictionary<string, Dictionary<Type, List<object>>>();

        /// <summary>
        /// 工作表名称映射到字典中以行号为键跟踪对象。
        /// </summary>
        public Dictionary<string, Dictionary<int, object>> Objects { get; } = new Dictionary<string, Dictionary<int, object>>();

        /// <summary>
        /// 工作簿
        /// </summary>
        public IWorkbook Workbook
        {
            get { return _workbook; }

            private set
            {
                if (value != _workbook)
                {
                    Objects.Clear();
                    TrackedColumns.Clear();
                    BuiltinStyles.Clear();
                    CustomStyles.Clear();
                }
                _workbook = value;
            }
        }

        /// <summary>
        /// 映射列时，此数组中的字符将从列标题中删除
        /// </summary>
        public char[] IgnoredNameChars { get; set; }

        /// <summary>
        /// 映射列时，列名将从此数组中的字符截断
        /// </summary>
        public char[] TruncateNameFrom { get; set; }

        /// <summary>
        /// 是否跟踪从Excel文件读取的对象。默认值为true。
        /// 如果启用了对象跟踪，对象将跟踪导入方法生成的对象。
        /// 修改这些对象保存Excel文件，无需指定要保存的对象列表。
        /// </summary>
        public bool TrackObjects { get; set; } = true;

        /// <summary>
        /// 是否将第一行作为列标题。默认值为true
        /// </summary>
        public bool HasHeader { get; set; } = true;

        /// <summary>
        /// 获取或设置第一行的从零开始的索引。如果未设置，它将自动检测。
        /// 如果HasHeader为true（默认情况下），则表示标题行索引
        /// </summary>
        public int FirstRowIndex { get; set; } = -1;

        #endregion

        #region Constructors

        /// <summary>
        /// 初始化
        /// </summary>
        public ExcelMapper()
        {
        }

        /// <summary>
        /// 使用流工作簿初始化
        /// </summary>
        public ExcelMapper(Stream stream)
        {
            if (stream == null)
                throw new ArgumentNullException(nameof(stream));

            using (stream)
            {
                Workbook = WorkbookFactory.Create(stream);
            }
        }

        /// <summary>
        /// 使用工作簿初始化
        /// </summary>
        public ExcelMapper(IWorkbook workbook)
        {
            if (workbook == null)
                throw new ArgumentNullException(nameof(workbook));

            Workbook = workbook;
        }

        /// <summary>
        /// 使用文件路径初始化
        /// </summary>
        public ExcelMapper(string filePath) : this(new FileStream(filePath, FileMode.Open))
        {
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// 自定义列解析器
        /// </summary>
        /// <param name="columnFilter">映射和未映射列的自定义逻辑</param>
        /// <param name="tryTake">导入列自定义逻辑</param>
        /// <param name="tryPut">导出自定义逻辑</param>
        public ExcelMapper Map(Func<IColumnInfo, bool> columnFilter, Func<IColumnInfo, object, bool> tryTake = null, Func<IColumnInfo, object, bool> tryPut = null)
        {
            _columnFilter = columnFilter;
            _defaultPutResolver = tryPut;
            _defaultTakeResolver = tryTake;

            return this;
        }

        /// <summary>
        /// 映射列属性
        /// </summary>
        /// <param name="attribute"></param>
        public ExcelMapper Map(ColumnAttribute attribute)
        {
            if (attribute == null) throw new ArgumentNullException(nameof(attribute));

            if (attribute.Property != null)
            {
                attribute.MergeTo(Attributes);
            }
            else if (attribute.PropertyName != null)
            {
                if (DynamicAttributes.ContainsKey(attribute.PropertyName))
                {
                    DynamicAttributes[attribute.PropertyName].MergeFrom(attribute);
                }
                else
                {
                    // Ensures column name for the first time mapping.
                    if (attribute.Name == null)
                    {
                        attribute.Name = attribute.PropertyName;
                    }

                    DynamicAttributes[attribute.PropertyName] = attribute;
                }
            }
            else
            {
                throw new InvalidOperationException("Either PropertyName or Property should be specified for a valid mapping!");
            }

            return this;
        }

        /// <summary>
        /// 指定为属性使用上面单元格中的最后一个非空值。
        //  通常用于解决合并单元中的空白单元格问题。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="propertySelector"></param>
        public ExcelMapper UseLastNonBlankValue<T>(Expression<Func<T, object>> propertySelector)
        {
            PropertyInfo pi = MapperHelper.GetPropertyInfoByExpression(propertySelector);
            if (pi == null) return this;
            new ColumnAttribute { Property = pi, UseLastNonBlankValue = true }.MergeTo(Attributes);

            return this;
        }

        /// <summary>
        /// 指定忽略属性,忽略的属性不会映射为导入和导出。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="propertySelector"></param>
        public ExcelMapper Ignore<T>(Expression<Func<T, object>> propertySelector)
        {
            PropertyInfo pi = MapperHelper.GetPropertyInfoByExpression(propertySelector);
            if (pi == null) return this;
            new ColumnAttribute { Property = pi, Ignored = true }.MergeTo(Attributes);

            return this;
        }



        /// <summary>
        /// 指定自定义格式
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="customFormat">自定义格式，参考 https://support.microsoft.com/zh-cn/office/%e5%88%9b%e5%bb%ba%e8%87%aa%e5%ae%9a%e4%b9%89%e6%95%b0%e5%ad%97%e6%a0%bc%e5%bc%8f-78f2a361-936b-4c03-8772-09fab54be7f4?ui=zh-CN&rs=zh-CN&ad=CN</param>
        /// <param name="propertySelector"></param>
        public ExcelMapper Format<T>(string customFormat, Expression<Func<T, object>> propertySelector)
        {
            PropertyInfo pi = MapperHelper.GetPropertyInfoByExpression(propertySelector);
            if (pi == null) return this;
            new ColumnAttribute { Property = pi, CustomFormat = customFormat }.MergeTo(Attributes);

            return this;
        }

        /// <summary>
        /// 设置导出标题单元格
        /// </summary>
        /// <param name="headerAction">导出标题单元格</param>
        public ExcelMapper ForHeader(Action<ICell> headerAction)
        {
            _headerAction = headerAction;
            return this;
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="sheetIndex">工作表索引</param>
        /// <param name="maxErrorRows">停止读取的最大错误行数</param>
        /// <param name="objectInitializer">新的实体对象</param>
        public IEnumerable<RowInfo<T>> Take<T>(int sheetIndex = 0, int maxErrorRows = 10, Func<T> objectInitializer = null) where T : class
        {
            ISheet sheet = Workbook.GetSheetAt(sheetIndex);
            return Take(sheet, maxErrorRows, objectInitializer);
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="maxErrorRows">停止读取的最大错误行数</param>
        /// <param name="objectInitializer">新的实体对象</param>
        public IEnumerable<RowInfo<T>> Take<T>(string sheetName, int maxErrorRows = 10, Func<T> objectInitializer = null) where T : class
        {
            ISheet sheet = Workbook.GetSheet(sheetName);
            return Take(sheet, maxErrorRows, objectInitializer);
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="objects">保存数据</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        public void Put<T>(IEnumerable<T> objects, string sheetName, bool overwrite = true)
        {
            if (Workbook == null) Workbook = new XSSFWorkbook();
            ISheet sheet = Workbook.GetSheet(sheetName) ?? Workbook.CreateSheet(sheetName);
            Put(sheet, objects, overwrite);
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="objects">保存数据</param>
        /// <param name="sheetIndex">工作表名称</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        public void Put<T>(IEnumerable<T> objects, int sheetIndex = 0, bool overwrite = true)
        {
            if (Workbook == null) Workbook = new XSSFWorkbook();
            ISheet sheet = Workbook.NumberOfSheets > sheetIndex ? Workbook.GetSheetAt(sheetIndex) : Workbook.CreateSheet();
            Put(sheet, objects, overwrite);
        }

        /// <summary>
        /// 保存工作簿
        /// </summary>
        /// <param name="path">保存路径</param>
        public void Save(string path)
        {
            if (Workbook == null) return;

            using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.Write))
                Workbook.Write(fs);
        }

        /// <summary>
        /// 保存工作簿
        /// </summary>
        /// <param name="stream">流工作簿</param>
        public void Save(Stream stream)
        {
            Workbook?.Write(stream);
        }

        /// <summary>
        /// 保存到指定的Excel文件中
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="path">保存路径</param>
        /// <param name="objects">保存数据</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        public void Save<T>(string path, IEnumerable<T> objects, string sheetName, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null && !overwrite) LoadWorkbookFromFile(path);

            using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.Write))
                Save(fs, objects, sheetName, overwrite, xlsx);
        }

        /// <summary>
        /// 保存到指定的Excel文件中
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="path">保存路径</param>
        /// <param name="objects">保存数据</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        public void Save<T>(string path, IEnumerable<T> objects, int sheetIndex = 0, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null && !overwrite) LoadWorkbookFromFile(path);

            using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.Write))
                Save(fs, objects, sheetIndex, overwrite, xlsx);
        }

        /// <summary>
        /// 保存到指定流
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="stream">写入的流</param>
        /// <param name="objects">保存数据</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        public void Save<T>(Stream stream, IEnumerable<T> objects, string sheetName, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null)
                Workbook = xlsx ? new XSSFWorkbook() : (IWorkbook)new HSSFWorkbook();
            ISheet sheet = Workbook.GetSheet(sheetName) ?? Workbook.CreateSheet(sheetName);
            Save(stream, sheet, objects, overwrite);
        }

        /// <summary>
        /// 保存到指定流
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="stream">写入的流</param>
        /// <param name="objects">保存数据</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        public void Save<T>(Stream stream, IEnumerable<T> objects, int sheetIndex = 0, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null)
                Workbook = xlsx ? new XSSFWorkbook() : (IWorkbook)new HSSFWorkbook();
            ISheet sheet = Workbook.NumberOfSheets > sheetIndex ? Workbook.GetSheetAt(sheetIndex) : Workbook.CreateSheet();
            Save(stream, sheet, objects, overwrite);
        }

        /// <summary>
        /// 将跟踪的对象保存到指定的Excel文件
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="path">保存路径</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        public void Save<T>(string path, string sheetName, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null && !overwrite) LoadWorkbookFromFile(path);

            using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.Write))
                Save<T>(fs, sheetName, overwrite, xlsx);
        }

        /// <summary>
        /// 将跟踪的对象保存到指定的Excel文件
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="path">保存路径</param>
        /// <param name="sheetIndex">工作表名称</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        public void Save<T>(string path, int sheetIndex = 0, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null && !overwrite) LoadWorkbookFromFile(path);

            using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.Write))
                Save<T>(fs, sheetIndex, overwrite, xlsx);
        }

        /// <summary>
        /// 将跟踪的对象保存到指定流
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="stream">写入流</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        public void Save<T>(Stream stream, string sheetName, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null)
                Workbook = xlsx ? new XSSFWorkbook() : (IWorkbook)new HSSFWorkbook();
            ISheet sheet = Workbook.GetSheet(sheetName) ?? Workbook.CreateSheet(sheetName);

            Save<T>(stream, sheet, overwrite);
        }

        /// <summary>
        /// 将跟踪的对象保存到指定流
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="stream">写入流</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        /// <param name="xlsx">true为xlsx，false为xls</param>
        public void Save<T>(Stream stream, int sheetIndex = 0, bool overwrite = true, bool xlsx = true)
        {
            if (Workbook == null)
                Workbook = xlsx ? new XSSFWorkbook() : (IWorkbook)new HSSFWorkbook();
            ISheet sheet = Workbook.GetSheetAt(sheetIndex) ?? Workbook.CreateSheet();

            Save<T>(stream, sheet, overwrite);
        }

        #endregion

        #region Private Methods

        #region Import

        /// <summary>
        /// 导入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="maxErrorRows"></param>
        /// <param name="objectInitializer"></param>
        /// <returns></returns>
        private IEnumerable<RowInfo<T>> Take<T>(ISheet sheet, int maxErrorRows, Func<T> objectInitializer = null) where T : class
        {
            if (sheet == null || sheet.PhysicalNumberOfRows < 1)
            {
                yield break;
            }

            int firstRowIndex = GetFirstRowIndex(sheet);
            IRow firstRow = sheet.GetRow(firstRowIndex);

            Type targetType = typeof(T);
            if (targetType == typeof(object)) // 动态类型
            {
                targetType = GetDynamicType(sheet);
                MapperHelper.LoadDynamicAttributes(Attributes, DynamicAttributes, targetType);
            }

            // 扫描对象属性
            MapperHelper.LoadAttributes(Attributes, targetType);

            // 读取第一行以获取列信息
            List<ColumnInfo> columns = GetColumns(firstRow, targetType);

            // 基于第一个非空单元格检测列格式
            MapperHelper.LoadDataFormats(sheet, HasHeader ? firstRowIndex + 1 : firstRowIndex, columns, TypeFormats);

            if (TrackObjects) Objects[sheet.SheetName] = new Dictionary<int, object>();

            // 循环文件中的行,为每行生成一个对象.
            int errorCount = 0;
            int firstDataRowIndex = HasHeader ? firstRowIndex + 1 : firstRowIndex;
            foreach (IRow row in sheet)
            {
                if (maxErrorRows > 0 && errorCount >= maxErrorRows) break;
                if (row.RowNum < firstDataRowIndex) continue;

                object obj = objectInitializer == null ? Activator.CreateInstance(targetType) : objectInitializer();
                RowInfo<T> rowInfo = new RowInfo<T>(row.RowNum, obj as T, -1, string.Empty);
                LoadRowData(columns, row, obj, rowInfo);

                if (rowInfo.ErrorColumnIndex >= 0)
                {
                    errorCount++;
                }
                if (TrackObjects) Objects[sheet.SheetName][row.RowNum] = rowInfo.Value;

                yield return rowInfo;
            }
        }

        /// <summary>
        /// 获取动态类型
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private Type GetDynamicType(ISheet sheet)
        {
            int firstRowIndex = GetFirstRowIndex(sheet);
            IRow firstRow = sheet.GetRow(firstRowIndex);

            Dictionary<string, Type> names = new Dictionary<string, Type>();

            foreach (ICell header in firstRow)
            {
                ColumnInfo column = GetColumnInfoByDynamicAttribute(header);
                Type type = MapperHelper.InferColumnDataType(sheet, HasHeader ? firstRowIndex : -1, header.ColumnIndex);

                if (column != null)
                {
                    names[column.Attribute.PropertyName] = type ?? typeof(string);
                }
                else
                {
                    object headerValue = GetHeaderValue(header);
                    ColumnInfo tempColumn = new ColumnInfo(headerValue, header.ColumnIndex, null);
                    if (_columnFilter != null && !_columnFilter(tempColumn))
                    {
                        continue;
                    }

                    string propertyName;
                    if (HasHeader && MapperHelper.GetCellType(header) == CellType.String)
                    {
                        propertyName = MapperHelper.GetVariableName(header.StringCellValue, IgnoredNameChars,
                            TruncateNameFrom, header.ColumnIndex);
                    }
                    else
                    {
                        propertyName = MapperHelper.GetVariableName(null, null, null, header.ColumnIndex);
                    }

                    names[propertyName] = type ?? typeof(string);
                    DynamicAttributes[propertyName] = new ColumnAttribute((ushort)header.ColumnIndex) { PropertyName = propertyName };
                }
            }

            return TypeFactory.CreateType(names, true);
        }

        /// <summary>
        /// 获取列集合
        /// </summary>
        /// <param name="headerRow"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private List<ColumnInfo> GetColumns(IRow headerRow, Type type)
        {
            string sheetName = headerRow.Sheet.SheetName;
            List<ColumnInfo> columns = new List<ColumnInfo>();
            List<object> columnsCache = new List<object>(); // 缓存以供导出使用

            // 按第一行准备ColumnInfo列表.
            foreach (ICell header in headerRow)
            {
                // 通过属性自定义映射
                ColumnInfo column = GetColumnInfoByAttribute(header, type);

                // 命名约定
                if (column == null && HasHeader && MapperHelper.GetCellType(header) == CellType.String)
                {
                    string s = header.StringCellValue;

                    if (!string.IsNullOrWhiteSpace(s))
                    {
                        column = GetColumnInfoByName(s.Trim(), header.ColumnIndex, type);
                    }
                }

                // 列过滤
                if (column == null)
                {
                    column = GetColumnInfoByFilter(header, _columnFilter);

                    if (column != null) // 设置默认解析程序，因为列未显式映射
                    {
                        column.Attribute.TryPut = _defaultPutResolver;
                        column.Attribute.TryTake = _defaultTakeResolver;
                    }
                }

                if (column == null) continue; // 没有属性映射到此列

                if (header.CellStyle != null) column.HeaderFormat = header.CellStyle.DataFormat;
                columns.Add(column);
                columnsCache.Add(column);
            }

            Dictionary<Type, List<object>> typeDict = TrackedColumns.ContainsKey(sheetName)
                ? TrackedColumns[sheetName]
                : TrackedColumns[sheetName] = new Dictionary<Type, List<object>>();

            typeDict[type] = columnsCache;

            return columns;
        }

        private ColumnInfo GetColumnInfoByDynamicAttribute(ICell header)
        {
            CellType cellType = MapperHelper.GetCellType(header);
            int index = header.ColumnIndex;

            foreach (KeyValuePair<string, ColumnAttribute> pair in DynamicAttributes)
            {
                var attribute = pair.Value;

                // 如果没有标题，则无法通过解析标题字符串获取ColumnInfo
                if (!HasHeader && attribute.Index < 0) continue;

                object headerValue = HasHeader ? GetHeaderValue(header) : null;
                bool indexMatch = attribute.Index == index;
                bool nameMatch = cellType == CellType.String && string.Equals(attribute.Name, header.StringCellValue);

                if (indexMatch || (attribute.Index < 0 && nameMatch))
                {
                    //使用克隆以避免污染原始属性
                    attribute = attribute.Clone(index);
                    return new ColumnInfo(headerValue, attribute);
                }
            }

            return null;
        }

        /// <summary>
        /// 根据列特性获取列映射信息
        /// </summary>
        /// <param name="header"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private ColumnInfo GetColumnInfoByAttribute(ICell header, Type type)
        {
            CellType cellType = MapperHelper.GetCellType(header);
            int index = header.ColumnIndex;

            foreach (KeyValuePair<PropertyInfo, ColumnAttribute> pair in Attributes)
            {
                var attribute = pair.Value;

                if (pair.Key.ReflectedType != type || attribute.Ignored == true) continue;

                // 如果没有标题，则无法通过解析标题字符串获取列映射信息。
                if (!HasHeader && attribute.Index < 0) continue;

                object headerValue = HasHeader ? GetHeaderValue(header) : null;
                bool indexMatch = attribute.Index == index;
                bool nameMatch = cellType == CellType.String &&
                                string.Equals(attribute.Name?.Trim(), header.StringCellValue?.Trim());


                if (indexMatch || (attribute.Index < 0 && nameMatch))
                {
                    //使用克隆以避免污染原始属性，
                    attribute = attribute.Clone(index);
                    return new ColumnInfo(headerValue, attribute);
                }
            }

            return null;
        }

        /// <summary>
        /// 获取列属性映射通过字符串单元格值
        /// </summary>
        /// <param name="name"></param>
        /// <param name="index"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private ColumnInfo GetColumnInfoByName(string name, int index, Type type)
        {
            // 按字符串搜索（忽略大小写）
            PropertyInfo pi = type.GetProperty(name, MapperHelper.BindingFlag);

            if (pi == null)
            {
                // 搜索DisplayAttribute的显示名称（如果有）
                foreach (PropertyInfo propertyInfo in type.GetProperties(MapperHelper.BindingFlag))
                {
                    object[] attributes = propertyInfo.GetCustomAttributes(typeof(DisplayAttribute), false);

                    if (attributes.Any(att => string.Equals(((DisplayAttribute)att).Name, name, StringComparison.CurrentCultureIgnoreCase)))
                    {
                        pi = propertyInfo;
                        break;
                    }
                }
            }

            if (pi == null)
            {
                // 删除忽略的字符并执行截断
                pi = type.GetProperty(MapperHelper.GetRefinedName(name, IgnoredNameChars, TruncateNameFrom), MapperHelper.BindingFlag);
            }

            if (pi == null) return null;

            ColumnAttribute attribute = null;

            if (Attributes.ContainsKey(pi))
            {
                attribute = Attributes[pi].Clone(index);
                if (attribute.Ignored == true) return null;
            }

            return attribute == null ? new ColumnInfo(name, index, pi) : new ColumnInfo(name, attribute);
        }

        /// <summary>
        /// 获取过滤列属性映射
        /// </summary>
        /// <param name="header"></param>
        /// <param name="columnFilter"></param>
        /// <returns></returns>
        private static ColumnInfo GetColumnInfoByFilter(ICell header, Func<IColumnInfo, bool> columnFilter)
        {
            if (columnFilter == null) return null;

            object headerValue = GetHeaderValue(header);
            ColumnInfo column = new ColumnInfo(headerValue, header.ColumnIndex, null);

            return !columnFilter(column) ? null : column;
        }

        /// <summary>
        /// 加载行数据
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="row"></param>
        /// <param name="target"></param>
        /// <param name="rowInfo"></param>
        private static void LoadRowData(IEnumerable<ColumnInfo> columns, IRow row, object target, IRowInfo rowInfo)
        {
            int errorIndex = -1;
            string errorMessage = null;

            void ColumnFailed(IColumnInfo column, string message)
            {
                if (errorIndex >= 0) return;
                if (column.Attribute.IgnoreErrors == true) return;
                errorIndex = column.Attribute.Index;
                errorMessage = message;
            }

            foreach (ColumnInfo column in columns)
            {
                var index = column.Attribute.Index;
                if (index < 0) continue;

                try
                {
                    ICell cell = row.GetCell(index);
                    var propertyType = column.Attribute.PropertyUnderlyingType ?? column.Attribute.Property?.PropertyType;

                    if (!MapperHelper.TryGetCellValue(cell, propertyType, out object valueObj))
                    {
                        ColumnFailed(column, "CellType is not supported yet!");
                        continue;
                    }

                    valueObj = column.RefreshAndGetValue(valueObj);

                    if (column.Attribute.TryTake != null)
                    {
                        if (!column.Attribute.TryTake(column, target))
                        {
                            ColumnFailed(column, "Returned failure by custom cell resolver!");
                        }
                    }
                    else if (propertyType != null)
                    {
                        // 类型转换
                        if (MapperHelper.TryConvertType(valueObj, column, out object result))
                        {
                            column.Attribute.Property.SetValue(target, result, null);
                        }
                        else
                        {
                            ColumnFailed(column, "Cannot convert value to the property type!");
                        }
                    }
                }
                catch (Exception e)
                {
                    ColumnFailed(column, e.ToString());
                }
            }

            rowInfo.ErrorColumnIndex = errorIndex;
            rowInfo.ErrorMessage = errorMessage;
        }

        /// <summary>
        /// 获取表头值
        /// </summary>
        /// <param name="header"></param>
        /// <returns></returns>
        private static object GetHeaderValue(ICell header)
        {
            object value;
            CellType cellType = header.CellType;

            if (cellType == CellType.Formula)
            {
                cellType = header.CachedFormulaResultType;
            }

            switch (cellType)
            {
                case CellType.Numeric:
                    value = header.NumericCellValue;
                    break;

                case CellType.String:
                    value = header.StringCellValue;
                    break;

                default:
                    value = null;
                    break;
            }

            return value;
        }
        /// <summary>
        /// 加载指定路径工作簿
        /// </summary>
        /// <param name="path"></param>
        private void LoadWorkbookFromFile(string path)
        {
            Workbook = WorkbookFactory.Create(new FileStream(path, FileMode.Open));
        }

        #endregion

        #region Export

        /// <summary>
        /// 导出
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="sheet">工作表</param>
        /// <param name="objects">保存的数据</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        private void Put<T>(ISheet sheet, IEnumerable<T> objects, bool overwrite)
        {
            string sheetName = sheet.SheetName;
            int firstRowIndex = GetFirstRowIndex(sheet);
            IRow firstRow = sheet.GetRow(firstRowIndex);
            T[] objectArray = objects as T[] ?? objects.ToArray();
            Type type = MapperHelper.GetConcreteType(objectArray);

            List<ColumnInfo> columns = GetTrackedColumns(sheetName, type) ??
                           GetColumns(firstRow ?? PopulateFirstRow(sheet, null, type), type);
            firstRow = sheet.GetRow(firstRowIndex) ?? PopulateFirstRow(sheet, columns, type);

            int rowIndex = overwrite
                ? HasHeader ? firstRowIndex + 1 : firstRowIndex
                : sheet.GetRow(sheet.LastRowNum) != null ? sheet.LastRowNum + 1 : sheet.LastRowNum;

            MapperHelper.EnsureDefaultFormats(columns, TypeFormats);

            foreach (T o in objectArray)
            {
                IRow row = sheet.GetRow(rowIndex);

                if (overwrite && row != null)
                {

                    row.Cells.Clear();
                }

                row = row ?? sheet.CreateRow(rowIndex);

                foreach (ColumnInfo column in columns)
                {
                    var pi = column.Attribute.Property;
                    var value = pi?.GetValue(o, null);
                    ICell cell = row.GetCell(column.Attribute.Index, MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    column.CurrentValue = value;
                    if (column.Attribute.TryPut == null || column.Attribute.TryPut(column, o))
                    {
                        SetCell(cell, column.CurrentValue, column, setStyle: overwrite);
                    }
                }

                rowIndex++;
            }

            // 删除未使用的行（如果有）
            while (overwrite && rowIndex <= sheet.LastRowNum)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row != null)
                {
                    row.Cells.Clear();
                }
                rowIndex++;
            }

            // 为标题注入自定义操作
            if (overwrite && HasHeader && _headerAction != null)
            {
                firstRow?.Cells.ForEach(c => _headerAction(c));
            }
        }

        /// <summary>
        /// 保存跟踪对象
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="stream">流</param>
        /// <param name="sheet">工作表</param>
        /// <param name="overwrite">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        private void Save<T>(Stream stream, ISheet sheet, bool overwrite)
        {
            string sheetName = sheet.SheetName;
            Dictionary<int, object> objects = Objects.ContainsKey(sheetName) ? Objects[sheetName] : new Dictionary<int, object>();

            Put(sheet, objects.Values.OfType<T>(), overwrite);
            Workbook.Write(stream);
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="stream">流</param>
        /// <param name="sheet">工作表</param>
        /// <param name="objects"></param>
        /// <param name="overwrite"></param>
        private void Save<T>(Stream stream, ISheet sheet, IEnumerable<T> objects, bool overwrite)
        {
            Put(sheet, objects, overwrite);
            Workbook.Write(stream);
        }
        /// <summary>
        /// 填充第一行
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="columns">列映射信息</param>
        /// <param name="type">类型</param>
        /// <returns></returns>
        private IRow PopulateFirstRow(ISheet sheet, List<ColumnInfo> columns, Type type)
        {
            IRow row = sheet.CreateRow(GetFirstRowIndex(sheet));

            // 使用现有列填充第一行

            if (columns != null)
            {
                foreach (ColumnInfo column in columns)
                {
                    ICell cell = row.CreateCell(column.Attribute.Index);

                    if (!HasHeader) continue;

                    SetCell(cell, column.Attribute.Name ?? column.HeaderValue, column, true);
                }

                return row;
            }

            // 如果未缓存任何列，则使用属性和对象属性填充第一行

            MapperHelper.LoadAttributes(Attributes, type);

            IEnumerable<KeyValuePair<PropertyInfo, ColumnAttribute>> attributes = Attributes.Where(p => p.Value.Property != null && p.Value.Property.ReflectedType == type);
            List<PropertyInfo> properties = new List<PropertyInfo>(type.GetProperties(MapperHelper.BindingFlag));

            // 首先为指定了属性的对象填充
            foreach (KeyValuePair<PropertyInfo, ColumnAttribute> pair in attributes)
            {
                PropertyInfo pi = pair.Key;
                var attribute = pair.Value;
                if (pair.Value.Index < 0) continue;

                ICell cell = row.CreateCell(attribute.Index);
                if (HasHeader)
                {
                    cell.SetCellValue(attribute.Name ?? pi.Name);
                }
                properties.Remove(pair.Key); // 删除已填充的属性
            }

            int index = 0;

            // 为未指定属性的对象填充
            foreach (PropertyInfo pi in properties)
            {
                var attribute = Attributes.ContainsKey(pi) ? Attributes[pi] : null;
                if (attribute?.Ignored == true) continue;

                while (row.GetCell(index) != null) index++;
                ICell cell = row.CreateCell(index);
                if (HasHeader)
                {
                    cell.SetCellValue(attribute?.Name ?? pi.Name);
                }
                else
                {
                    new ColumnAttribute { Index = index, Property = pi }.MergeTo(Attributes);
                }
                index++;
            }

            return row;
        }

        /// <summary>
        /// 获取跟踪对象列映射属性
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private List<ColumnInfo> GetTrackedColumns(string sheetName, Type type)
        {
            if (!TrackedColumns.ContainsKey(sheetName)) return null;

            IEnumerable<ColumnInfo> columns = null;

            Dictionary<Type, List<object>> cols = TrackedColumns[sheetName];
            if (cols.ContainsKey(type))
            {
                columns = cols[type].OfType<ColumnInfo>();
            }

            return columns?.ToList();
        }

        /// <summary>
        /// 设置单元格值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="value">值</param>
        /// <param name="column">列映射信息</param>
        /// <param name="isHeader">是否标题</param>
        /// <param name="setStyle">要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false</param>
        private void SetCell(ICell cell, object value, ColumnInfo column, bool isHeader = false, bool setStyle = true)
        {
            if (value == null || value is ICollection)
            {
                cell.SetCellValue((string)null);
            }
            else if (value is DateTime)
            {
                cell.SetCellValue((DateTime)value);
            }
            else if (value.GetType().IsNumeric())
            {
                cell.SetCellValue(Convert.ToDouble(value));
            }
            else if (value is bool)
            {
                cell.SetCellValue((bool)value);
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }

            if (column != null && setStyle)
            {
                column.SetCellStyle(cell, BuiltinStyles, CustomStyles, value, isHeader, TypeFormats);
            }
        }

        #endregion Export

        /// <summary>
        /// 获取第一行索引
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private int GetFirstRowIndex(ISheet sheet) => FirstRowIndex >= 0 ? FirstRowIndex : sheet.FirstRowNum;

        #endregion
    }
}
